﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Register.IDAL;
using Register.DBUtility;
using System.Data.SqlClient;
using System.Data;
using Register.Model;

namespace Register.DAL
{
    /// <summary>
    /// FileName: SchedulingInfoDAL.cs
    /// CLRVersion: 4.0.30319.269
    /// Author: WangKunLun
    /// Corporation: 
    /// Description: 
    /// DateTime: 2012/6/30 17:39:34
    /// </summary>
    public class SchedulingInfoDAL : ISchedulingInfoDAL
    {
        private string tableName = "System_schedulingInfo";

        public int AddSchedulingInfo(Model.SchedulingInfo model)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into System_schedulingInfo(");
            strSql.Append("SchedulingInfo_ID,Departments_ID,Doctor_ID,SchedulingInfo_TimeType,SchedulingInfo_DayTime,SchedulingInfo_OutpatientType,SchedulingInfo_RegisteredNumber,SchedulingInfo_ConfirmStatus,SchedulingInfo_Status,SchedulingInfo_AddTime,SchedulingInfo_Recoder,SchedulingInfo_LastAmendTime,SchedulingInfo_LastAmendPerson)");
            strSql.Append(" values (");
            strSql.Append("@SchedulingInfo_ID,@Departments_ID,@Doctor_ID,@SchedulingInfo_TimeType,@SchedulingInfo_DayTime,@SchedulingInfo_OutpatientType,@SchedulingInfo_RegisteredNumber,@SchedulingInfo_ConfirmStatus,@SchedulingInfo_Status,@SchedulingInfo_AddTime,@SchedulingInfo_Recoder,@SchedulingInfo_LastAmendTime,@SchedulingInfo_LastAmendPerson)");
            SqlParameter[] parameters = {
					new SqlParameter("@SchedulingInfo_ID", SqlDbType.Char,14),
					new SqlParameter("@Departments_ID", SqlDbType.Char,14),
					new SqlParameter("@Doctor_ID", SqlDbType.Char,14),
					new SqlParameter("@SchedulingInfo_TimeType", SqlDbType.Char,4),
					new SqlParameter("@SchedulingInfo_DayTime", SqlDbType.DateTime),
					new SqlParameter("@SchedulingInfo_OutpatientType", SqlDbType.Char,4),
					new SqlParameter("@SchedulingInfo_RegisteredNumber", SqlDbType.Int),
					new SqlParameter("@SchedulingInfo_ConfirmStatus", SqlDbType.Char,1),
					new SqlParameter("@SchedulingInfo_Status", SqlDbType.Char,1),
					new SqlParameter("@SchedulingInfo_AddTime", SqlDbType.DateTime),
					new SqlParameter("@SchedulingInfo_Recoder", SqlDbType.VarChar,20),
					new SqlParameter("@SchedulingInfo_LastAmendTime", SqlDbType.DateTime),
					new SqlParameter("@SchedulingInfo_LastAmendPerson", SqlDbType.VarChar,20)};
            parameters[0].Value = model.SchedulingInfo_ID;
            parameters[1].Value = model.Departments_ID;
            parameters[2].Value = model.Doctor_ID;
            parameters[3].Value = model.SchedulingInfo_TimeType;
            parameters[4].Value = model.SchedulingInfo_DayTime;
            parameters[5].Value = model.SchedulingInfo_OutpatientType;
            parameters[6].Value = model.SchedulingInfo_RegisteredNumber;
            parameters[7].Value = model.SchedulingInfo_ConfirmStatus;
            parameters[8].Value = model.SchedulingInfo_Status;
            parameters[9].Value = model.SchedulingInfo_AddTime;
            parameters[10].Value = model.SchedulingInfo_Recoder;
            parameters[11].Value = model.SchedulingInfo_LastAmendTime;
            parameters[12].Value = model.SchedulingInfo_LastAmendPerson;

            return DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);

        }

        public int UpdateSchedulingInfo(Model.SchedulingInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update System_schedulingInfo set ");
            strSql.Append("Departments_ID=@Departments_ID,");
            strSql.Append("Doctor_ID=@Doctor_ID ");
            strSql.Append("SchedulingInfo_TimeType=@SchedulingInfo_TimeType ");
            strSql.Append("SchedulingInfo_DayTime=@SchedulingInfo_DayTime ");
            strSql.Append("SchedulingInfo_OutpatientType=@SchedulingInfo_OutpatientType ");
            strSql.Append("SchedulingInfo_RegisteredNumber=@SchedulingInfo_RegisteredNumber ");
            strSql.Append("SchedulingInfo_ConfirmStatus=@SchedulingInfo_ConfirmStatus ");
            strSql.Append("SchedulingInfo_LastAmendTime=@SchedulingInfo_LastAmendTime ");
            strSql.Append("SchedulingInfo_LastAmendPerson=@SchedulingInfo_LastAmendPerson ");
            strSql.Append(" where SchedulingInfo_ID=@SchedulingInfo_ID ");
            SqlParameter[] parameters = {
                    new SqlParameter("@Departments_ID", SqlDbType.Char,14),
					new SqlParameter("@Doctor_ID", SqlDbType.Char,14),
					new SqlParameter("@SchedulingInfo_TimeType", SqlDbType.Char,4),
					new SqlParameter("@SchedulingInfo_DayTime", SqlDbType.DateTime),
					new SqlParameter("@SchedulingInfo_OutpatientType", SqlDbType.Char,4),
					new SqlParameter("@SchedulingInfo_RegisteredNumber", SqlDbType.Int),
					new SqlParameter("@SchedulingInfo_ConfirmStatus", SqlDbType.Char,1),
					new SqlParameter("@SchedulingInfo_LastAmendTime", SqlDbType.DateTime),
					new SqlParameter("@SchedulingInfo_LastAmendPerson", SqlDbType.VarChar,20),
                    new SqlParameter("@SchedulingInfo_ID", SqlDbType.Char,14)};
            parameters[0].Value = model.Departments_ID;
            parameters[1].Value = model.Doctor_ID;
            parameters[2].Value = model.SchedulingInfo_TimeType;
            parameters[3].Value = model.SchedulingInfo_DayTime;
            parameters[4].Value = model.SchedulingInfo_OutpatientType;
            parameters[5].Value = model.SchedulingInfo_RegisteredNumber;
            parameters[6].Value = model.SchedulingInfo_ConfirmStatus;
            parameters[7].Value = model.SchedulingInfo_LastAmendTime;
            parameters[8].Value = model.SchedulingInfo_LastAmendPerson;
            parameters[9].Value = model.SchedulingInfo_ID;
            return DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
        }

        public int DeleteSchedulingInfo(string SchedulingInfoID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update System_schedulingInfo set ");
            strSql.Append("SchedulingInfo_Status=0 ");
            strSql.Append(" where 1=@SchedulingInfo_ID ");
            SqlParameter[] parameters = {
					new SqlParameter("@SchedulingInfo_ID", SqlDbType.Char,14)};
            parameters[0].Value = SchedulingInfoID;

            return DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
        }

        public List<Model.SchedulingInfo> GetPagedSchedulingInfo(int size, int pageIndex, string where)
        {
            return DataSetToList(DbHelperSQL.getPagedData(" * ", " SchedulingInfo_DayTime ", where, tableName, OrderBy.DESC, pageIndex, size));
        }


        public Model.SchedulingInfo GetSchedulingInfo(string SchedulingInfoID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 SchedulingInfo_ID,Departments_ID,Doctor_ID,SchedulingInfo_TimeType,SchedulingInfo_DayTime,SchedulingInfo_OutpatientType,SchedulingInfo_RegisteredNumber,SchedulingInfo_ConfirmStatus,SchedulingInfo_Status,SchedulingInfo_AddTime,SchedulingInfo_Recoder,SchedulingInfo_LastAmendTime,SchedulingInfo_LastAmendPerson from System_schedulingInfo ");
            strSql.Append(" where SchedulingInfo_ID=@SchedulingInfo_ID ");
            SqlParameter[] parameters = {
					new SqlParameter("@SchedulingInfo_ID", SqlDbType.Char,14)};
            parameters[0].Value = SchedulingInfoID;
            return DataSetToList(DbHelperSQL.Query(strSql.ToString(), parameters)).First();
        }


        private List<Model.SchedulingInfo> DataSetToList(DataSet ds)
        {
            List<Model.SchedulingInfo> list = new List<Model.SchedulingInfo>();
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                SchedulingInfo SchedulingInfo = new SchedulingInfo();
                SchedulingInfo.SchedulingInfo_ID = (String)dr["SchedulingInfo_ID"];
                SchedulingInfo.Departments_ID = (String)dr["Departments_ID"];
                SchedulingInfo.Doctor_ID = (String)dr["Doctor_ID"];
                SchedulingInfo.SchedulingInfo_TimeType = (String)dr["SchedulingInfo_TimeType"];
                SchedulingInfo.SchedulingInfo_DayTime = (DateTime)dr["SchedulingInfo_DayTime"];
                SchedulingInfo.SchedulingInfo_OutpatientType = (String)dr["SchedulingInfo_OutpatientType"];
                SchedulingInfo.SchedulingInfo_RegisteredNumber = (int)dr["SchedulingInfo_RegisteredNumber"];
                SchedulingInfo.SchedulingInfo_ConfirmStatus = (String)dr["SchedulingInfo_ConfirmStatus"];
                SchedulingInfo.SchedulingInfo_Status = (String)dr["SchedulingInfo_Status"];
                SchedulingInfo.SchedulingInfo_AddTime = (DateTime)dr["SchedulingInfo_AddTime"];
                SchedulingInfo.SchedulingInfo_Recoder = (String)dr["SchedulingInfo_Recoder"];
                SchedulingInfo.SchedulingInfo_LastAmendTime = (DateTime)dr["SchedulingInfo_LastAmendTime"];
                SchedulingInfo.SchedulingInfo_LastAmendPerson = (String)dr["SchedulingInfo_LastAmendPerson"];
                list.Add(SchedulingInfo);
            }
            return list;
        }

        public int GetSchedulingInfoCount(string where)
        {
            int count = 0;
            StringBuilder str = new StringBuilder();
            str.Append(" select count(*) from ");
            str.Append(tableName);
            if (where != null)
            {
                str.Append(" where ");
                str.Append(where);
                count = (int)DbHelperSQL.GetSingle(str.ToString());
            }
            else
            {
                count = (int)DbHelperSQL.GetSingle(str.ToString());
            }
            return count;
        }

        public List<Model.DoctorInfo> GetDoctorInfo(String where)
        {
            String sql = " select Doctor_ID,Doctor_Name,Doctor_Type from System_doctorInfo  where Doctor_State=1 and "+where;

            return ChangeReaderToList(DbHelperSQL.ExecuteReader(sql));
        }

        private List<DoctorInfo> ChangeReaderToList(SqlDataReader reader)
        {
            List<DoctorInfo> list = new List<DoctorInfo>();
            while (reader.Read())
            {
                DoctorInfo doctorInfo = new DoctorInfo();
                doctorInfo.Doctor_ID = reader.GetString(0);
                doctorInfo.Doctor_Name = reader.GetString(1);
                doctorInfo.Doctor_Type = reader.GetString(2);
                doctorInfo.Value = 0;
                list.Add(doctorInfo);
            }
            return list;
        }

        private List<Model.DoctorInfo> DataSetToList1(DataSet ds)
        {
            List<Model.DoctorInfo> list = new List<Model.DoctorInfo>();
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                DoctorInfo doctor = new DoctorInfo();
                doctor.Doctor_ID = (string)dr["Doctor_ID"];
                doctor.Departments_ID = (string)dr["Departments_ID"];
                doctor.Doctor_No = (string)dr["Doctor_No"];
                doctor.Doctor_Name = (string)dr["Doctor_Name"];
                doctor.Doctor_Gender = (string)dr["Doctor_Gender"];
                doctor.Doctor_DateOfBirth = (DateTime)dr["Doctor_DateOfBirth"];
                doctor.Doctor_Position = (string)dr["Doctor_Position"];
                doctor.Doctor_Education = (string)dr["Doctor_Education"];
                doctor.Doctor_Specialty = (string)dr["Doctor_Specialty"];
                doctor.Doctor_Resume = (string)dr["Doctor_Resume"];
                doctor.Doctor_Phone = (string)dr["Doctor_Phone"];
                doctor.Doctor_Email = (string)dr["Doctor_Email"];
                doctor.Doctor_Picture = (string)dr["Doctor_Picture"];
                doctor.Doctor_AddTime = (DateTime)dr["Doctor_AddTime"];
                doctor.Doctor_Recoder = (string)dr["Doctor_Recoder"];
                doctor.Doctor_State = (string)dr["Doctor_State"];

                list.Add(doctor);
            }
            return list;
        }
        #region 获取医生安排信息
        public List<Model.SchedulingInfo> GetPagedScheduling(int size, int pageIndex, string where)
        {
            return DataSetToList2(DbHelperSQL.getPagedData(" * ", "SchedulingInfo_AddTime", where, tableName, OrderBy.DESC, pageIndex, size));
        }
        public int GetSchedulingInfoCount1(string where)
        {
            int count = 0;
            StringBuilder str = new StringBuilder();
            str.Append(" select count(*) from ");
            str.Append(tableName);
            if (where != null)
            {
                str.Append(" where ");
                str.Append(where);
                count = (int)DbHelperSQL.GetSingle(str.ToString());
            }
            else
            {
                count = (int)DbHelperSQL.GetSingle(str.ToString());
            }
            return count;

        }
        private List<Model.SchedulingInfo> DataSetToList2(DataSet ds)
        {
            List<Model.SchedulingInfo> list = new List<Model.SchedulingInfo>();
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                SchedulingInfo sch = new SchedulingInfo();
                sch.Departments_ID = (string)dr["Departments_ID"];
                sch.Doctor_ID = (string)dr["Doctor_ID"];
                sch.SchedulingInfo_AddTime = (DateTime)dr["SchedulingInfo_AddTime"];
                sch.SchedulingInfo_ConfirmStatus = (string)dr["SchedulingInfo_ConfirmStatus"];
                sch.SchedulingInfo_DayTime = (DateTime)dr["SchedulingInfo_DayTime"];
                sch.SchedulingInfo_ID = (string)dr["SchedulingInfo_ID"];
                //sch.SchedulingInfo_LastAmendPerson = (string)dr["SchedulingInfo_LastAmendPerson"];
                // sch.SchedulingInfo_LastAmendTime = (DateTime)dr["SchedulingInfo_LastAmendTime"];
                sch.SchedulingInfo_OutpatientType = (string)dr["SchedulingInfo_OutpatientType"];
                sch.SchedulingInfo_Recoder = (string)dr["SchedulingInfo_Recoder"];
                sch.SchedulingInfo_RegisteredNumber = (int)dr["SchedulingInfo_RegisteredNumber"];
                sch.SchedulingInfo_Status = (string)dr["SchedulingInfo_Status"];
                sch.SchedulingInfo_TimeType = (string)dr["SchedulingInfo_TimeType"];
                list.Add(sch);
            }
            return list;
        }
        #endregion
        #region 确认操作
        public int UpdateScheduling(SchedulingInfo schInfo)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("update System_schedulingInfo set ");
            strSql.Append("SchedulingInfo_ConfirmStatus=@SchedulingInfo_ConfirmStatus,");
            strSql.Append("SchedulingInfo_LastAmendTime=@SchedulingInfo_LastAmendTime,");
            strSql.Append("SchedulingInfo_LastAmendPerson=@SchedulingInfo_LastAmendPerson  ");

            strSql.Append(" where SchedulingInfo_ID=@SchedulingInfo_ID");
            SqlParameter[] parameters = {	
					new SqlParameter("@SchedulingInfo_ConfirmStatus", SqlDbType.Char,1),
					new SqlParameter("@SchedulingInfo_LastAmendTime", SqlDbType.DateTime),
					new SqlParameter("@SchedulingInfo_LastAmendPerson", SqlDbType.VarChar,20),
					new SqlParameter("@SchedulingInfo_ID",SqlDbType.Char,14)
                                       };

            parameters[0].Value = schInfo.SchedulingInfo_ConfirmStatus;

            parameters[1].Value = schInfo.SchedulingInfo_LastAmendTime;
            parameters[2].Value = schInfo.SchedulingInfo_LastAmendPerson;
            parameters[3].Value = schInfo.SchedulingInfo_ID;

            return DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);

        }
        #endregion
        #region 添加调整申请
        public int AddSchedulingAdjustment(Model.SchedulingAdjustmentInfo schAdjustmentInfo)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into System_schedulingAdjustment(");
            strSql.Append("SchedulingAdjustment_ID,SchedulingInfo_ID,SchedulingAdjustment_TimeTye,SchedulingAdjustment_DayTime,SchedulingAdjustment_OutpaientType,SchedulingAdjustment_RegisteredNumber,SchedulingAdjustment_Note,SchedulingAdjustment_Status,SchedulingAdjustment_AddTime,SchedulingAdjustment_Recoder,SchedulingAdjustment_VerifyStatus)");
            strSql.Append(" values (");
            strSql.Append("@SchedulingAdjustment_ID,@SchedulingInfo_ID,@SchedulingAdjustment_TimeTye,@SchedulingAdjustment_DayTime,@SchedulingAdjustment_OutpaientType,@SchedulingAdjustment_RegisteredNumber,@SchedulingAdjustment_Note,@SchedulingAdjustment_Status,@SchedulingAdjustment_AddTime,@SchedulingAdjustment_Recoder,@SchedulingAdjustment_VerifyStatus)");
            SqlParameter[] parameters = {
					new SqlParameter("@SchedulingAdjustment_ID", SqlDbType.Char,14),
					new SqlParameter("@SchedulingInfo_ID", SqlDbType.Char,14),
					new SqlParameter("@SchedulingAdjustment_TimeTye", SqlDbType.Char,4),
					new SqlParameter("@SchedulingAdjustment_DayTime", SqlDbType.DateTime),
					new SqlParameter("@SchedulingAdjustment_OutpaientType", SqlDbType.Char,4),
					new SqlParameter("@SchedulingAdjustment_RegisteredNumber", SqlDbType.Int),
					new SqlParameter("@SchedulingAdjustment_Note", SqlDbType.VarChar,300),
                    new SqlParameter("@SchedulingAdjustment_Status", SqlDbType.Char,1),
                    new SqlParameter("@SchedulingAdjustment_AddTime", SqlDbType.DateTime),
                    new SqlParameter("@SchedulingAdjustment_Recoder", SqlDbType.VarChar,20),
                    new SqlParameter("@SchedulingAdjustment_VerifyStatus", SqlDbType.Char,1)
                  
                                      };
            parameters[0].Value = schAdjustmentInfo.SchedulingAdjustment_ID;
            parameters[1].Value = schAdjustmentInfo.SchedulingInfo_ID;
            parameters[2].Value = schAdjustmentInfo.SchedulingAdjustment_TimeTye;
            parameters[3].Value = schAdjustmentInfo.SchedulingAdjustment_DayTime;
            parameters[4].Value = schAdjustmentInfo.SchedulingAdjustment_OutpaientType;
            parameters[5].Value = schAdjustmentInfo.SchedulingAdjustment_RegisteredNumber;
            parameters[6].Value = schAdjustmentInfo.SchedulingAdjustment_Note;
            parameters[7].Value = schAdjustmentInfo.SchedulingAdjustment_Status;
            parameters[8].Value = schAdjustmentInfo.SchedulingAdjustment_AddTime;
            parameters[9].Value = schAdjustmentInfo.SchedulingAdjustment_Recoder;
            parameters[10].Value = schAdjustmentInfo.SchedulingAdjustment_VerifyStatus;


            int flag = (int)DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (flag > 0)
            {
                string strSql1 = "update System_schedulingInfo set SchedulingInfo_ConfirmStatus=2";
                return (int)DbHelperSQL.ExecuteSql(strSql1);
            }
            else return -1;

        }
        #endregion
    }
}
